﻿using Abp.UI;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Threading.Tasks;
using System.Data.OracleClient;
using System.Text;

namespace SOEI.Solution.Helper
{

    public static class EntityFrameworkCoreExtensions
    {
        private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection, CommandType commandType, params object[] parameters)
        {
            var conn = facade.GetDbConnection();
            connection = conn;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(parameters);
            return cmd;
        }

        public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            var command = CreateCommand(facade, sql, out DbConnection conn, CommandType.Text, parameters);
            var reader = command.ExecuteReader();
            var dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            //conn.Close();
            return dt;
        }

        public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
        {
            var dt = SqlQuery(facade, sql, parameters);
            return dt.ToList<T>();
        }

        public static List<T> ToList<T>(this DataTable dt) where T : class, new()
        {
            var propertyInfos = typeof(T).GetProperties();

            var list = new List<T>();

            var vType = typeof(T);



            foreach (DataRow row in dt.Rows)
            {
                if (vType == typeof(object))
                {
                    dynamic obj = new ExpandoObject();
                    var dict = obj as IDictionary<string, object>;

                    foreach (DataColumn item in row.Table.Columns)
                    {
                        dict[item.ColumnName] = row[item.ColumnName];
                    }

                    list.Add(obj);
                }
                else
                {
                    var t = new T();
                    foreach (PropertyInfo p in vType.GetProperties())
                    {
                        if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                            p.SetValue(t, row[p.Name], null);
                    }
                    list.Add(t);
                }
            }
            
            return list;
        }

        /// <summary>
        /// 获取查询的第一行第一列数据
        /// </summary>
        /// <param name="facade"></param>
        /// <param name="sql">查询sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            var dt = SqlQuery(facade, sql, parameters);

            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0];
            }
            else
            {
                return null;
            }
        }

        public static async Task<DataSet> ExecuteStoreProcedure(this DatabaseFacade facade, string pProcedureName, ParamCollect pParam, params string[] pTables)
        {
            var command = CreateCommand(facade, pProcedureName, out DbConnection conn, CommandType.StoredProcedure);

            List<DbParameter> vParamOut = new List<DbParameter>();

            if(pParam != null)
            {
                if(pParam["OS_RETURN"] == null)
                {
                    pParam.Add(new ParamItem() { Name = "OS_RETURN", Value = 0, Direction = ParameterDirection.Output });
                }

                if(pParam["OS_ERRORINF"] == null)
                {
                    pParam.Add(new ParamItem() { Name = "OS_ERRORINF", Value = "", Direction = ParameterDirection.Output });
                }

                foreach (var item in pParam)
                {
                    var vParam = command.CreateParameter();

                    vParam.ParameterName = item.Name;
                    vParam.Value = item.Value;

                    if (vParam.Value == null)
                    {
                        vParam.Value = DBNull.Value;
                    }

                    vParam.Size = item.Size;
                    vParam.Direction = item.Direction;

                    command.Parameters.Add(vParam);

                    if (vParam.Direction != ParameterDirection.Input)
                    {
                        vParamOut.Add(vParam);
                    }
                }
            }

            if (facade.IsOracle())
            {
                if (pTables != null)
                {
                    foreach (var item in pTables)
                    {
                        OracleParameter vParam = new OracleParameter();

                        vParam.ParameterName = "OC_" + item;
                        vParam.Direction = ParameterDirection.Output;
                        vParam.OracleDbType = OracleDbType.RefCursor;
                        command.Parameters.Add(vParam);
                    }
                }

                (command as OracleCommand).BindByName = true;
            }

            var reader = await command.ExecuteReaderAsync();

            var ds = new DataSet();

            ds.Load(reader, LoadOption.OverwriteChanges, pTables);

            if (vParamOut != null && vParamOut.Count > 0)
            {
                foreach (var item in vParamOut)
                {
                    pParam[item.ParameterName].Value = item.Value;
                }
            }

            var vRes = Convert.ToInt32(pParam["OS_RETURN"].Value);

            if (vRes == -1)
            {
                throw new UserFriendlyException(pParam["OS_ERRORINF"].Value as string);
            }

            return ds;
        }

        #region 获取Lob对象

        private static OracleBlob GetOracleBlob(this DatabaseFacade facade, byte[] pData)
        {
            OracleBlob tempLob;
            string cmdText = "DECLARE xx blob; BEGIN DBMS_LOB.CREATETEMPORARY";
            cmdText += "(xx, false, 0); :tempBlob := xx; END;";

            var vParam = new OracleParameter("tempBlob", OracleDbType.Blob);
            vParam.Direction = ParameterDirection.Output;

            var oracleCommand = CreateCommand(facade, cmdText, out DbConnection conn, CommandType.Text, vParam);

            oracleCommand.CommandText = cmdText;

            oracleCommand.ExecuteNonQuery();

            tempLob = (OracleBlob)oracleCommand.Parameters[0].Value;

            tempLob.Write(pData, 0, pData.Length);

            return tempLob;
        }

        private static OracleClob GetOracleClob(this DatabaseFacade facade, string pData)
        {
            OracleClob tempLob;
            string cmdText;

            byte[] tempbuff = Encoding.Default.GetBytes(pData);

            cmdText = "DECLARE xx clob; BEGIN DBMS_LOB.CREATETEMPORARY";
            cmdText += "(xx, false,0); :tempBlob := xx; END;";

            var vParam = new OracleParameter("tempBlob", OracleDbType.Blob);
            vParam.Direction = ParameterDirection.Output;

            var oracleCommand = CreateCommand(facade, cmdText, out DbConnection conn, CommandType.Text, vParam);

            oracleCommand.CommandText = cmdText;

            oracleCommand.ExecuteNonQuery();

            tempLob = (OracleClob)oracleCommand.Parameters[0].Value;

            tempLob.Write(tempbuff, 0, tempbuff.Length);


            return tempLob;
        }

        #endregion

        /// <summary>执行存储过程并可传递输入输出参数
        /// </summary>
        /// <param name="facade"></param>
        /// <param name="pProcedureName">存储过程名称</param>
        /// <param name="pIn">输入参数</param>
        /// <param name="pOut">输出参数</param>
        /// <returns></returns>
        public static async Task<int> ExecuteStoreProcedure(this DatabaseFacade facade, string pProcedureName, ParamCollect pParam)
        {

            var command = CreateCommand(facade, pProcedureName, out DbConnection conn, CommandType.StoredProcedure);

            List<DbParameter> vParamOut = new List<DbParameter>();

            if (pParam != null)
            {
                if (pParam["OS_RETURN"] == null)
                {
                    pParam.Add(new ParamItem() { Name = "OS_RETURN", Value = 0, Direction = ParameterDirection.Output });
                }

                if (pParam["OS_ERRORINF"] == null)
                {
                    pParam.Add(new ParamItem() { Name = "OS_ERRORINF", Value = "", Direction = ParameterDirection.Output });
                }

                foreach (var item in pParam)
                {

                    DbParameter vParam = null;

                    if (facade.IsOracle() && item.Size > 4000)
                    {
                        var vOracleParam = new OracleParameter();

                        if(item.Value is byte[])
                        {
                            vOracleParam.OracleDbType = OracleDbType.Blob;
                            vOracleParam.Value = facade.GetOracleBlob(item.Value as byte[]);
                        }
                        else if(item.Value is string)
                        {
                            vOracleParam.OracleDbType = OracleDbType.Clob;
                            vOracleParam.Value = facade.GetOracleClob(item.Value as string);
                        }
                        
                        vParam = vOracleParam;
                    }
                    else
                    {
                        vParam = command.CreateParameter();
                        vParam.Value = item.Value;
                    }

                    vParam.ParameterName = item.Name;
                    vParam.Size = item.Size;
                    vParam.Direction = item.Direction;

                    command.Parameters.Add(vParam);

                    if (vParam.Direction != ParameterDirection.Input)
                    {
                        vParamOut.Add(vParam);
                    }
                }
            }

            await command.ExecuteNonQueryAsync();

            if (vParamOut != null && vParamOut.Count > 0)
            {
                foreach (var item in vParamOut)
                {
                    pParam[item.ParameterName].Value = item.Value;
                }
            }

            var vRes = Convert.ToInt32(pParam["OS_RETURN"].Value);

            if(vRes == -1)
            {
                throw new UserFriendlyException(pParam["OS_ERRORINF"].Value as string);
            }

            return vRes;
        }
    }

}
